/*
 * @Date: 2023-01-22 02:17:37
 * @LastEditors: 恶名扬 2080156129@qq.com
 * @LastEditTime: 2023-01-24 06:45:06
 * @FilePath: \campus-repair-platform\server\common\util.js
 */
/**
 * select   查询
 * selectLike   模糊查询
 * selectCount  查询总数
 * selectPhone  查询所有手机号
 * selectCount  总数查询
 * selectLimit  分页查询
 * selectCountLike 模糊查询总数
 * selectLikeLimit  分页模糊查询
 * selectQueryCountLike 模糊查询+筛选总数
 * selectQueryLikeLimit 分页模糊查询+筛选
 * idSelect 根据id查询
 * insert   添加
 * idUpdate     根据Id修改信息
 * phoneUpdatePwd  根据手机号修改密码
 * delete       根据id删除
 */

exports.select = (field, table, condition = "", order = 'id') =>
  `select HEX(id) id, ${field} from ${table} where 1=1 ${condition} order by ${order}`

exports.selectPhone = (phone) =>
  `SELECT HEX(id) id, phone FROM student WHERE phone = ${phone}
    UNION ALL SELECT HEX(id) id, phone FROM administrator WHERE phone = ${phone}
    UNION ALL SELECT HEX(id) id, phone FROM repairer WHERE phone = ${phone}`

exports.selectCount = (table, condition) =>
  `select count(*) as total from ${table} where 1=1 ${condition}`

exports.selectGroup = (table, condition, group = 'id') =>
  `select ${group}, count(*) as cont from ${table} where 1=1 ${condition} group by ${group}`

exports.selectLimit = (
  field,
  table,
  condition,
  page,
  pagesize,
  sort = false
) => {
  return `select HEX(id) id, ${field} from ${table} 
    where 1=1 ${condition} 
    ${sort ? "order by " + sort : ""}
    limit ${(page - 1) * pagesize}, ${page * pagesize}`
}

exports.selectCountLike = (table, query, d) =>
  `select count(*) as total from ${table} where 1=1 ${d ? query + ' like "%' + d + '%"' : ''}`

exports.selectLikeLimit = (
  field,
  table,
  query,
  d,
  page,
  pagesize,
  sort = false
) => {
  return `select HEX(id) id, ${field} from ${table} 
    where 1=1 ${d ? query + ' like "%' + d + '%"' : ''}
    ${sort ? "order by id desc" : ""}
    limit ${(page - 1) * pagesize}, ${pagesize}`
}

exports.selectQueryCountLike = (table, query, d, condition) =>
  `select count(*) as total from ${table} where 1=1 ${condition} ${d ? query + ' like "%' + d + '%"' : ''}`

exports.selectQueryLikeLimit = (
  field,
  table,
  query,
  d,
  page,
  pagesize,
  condition,
  sort = false
) => {
  return `select HEX(id) id, ${field} from ${table} 
    where 1=1 
    ${condition} ${d ? query + ' like "%' + d + '%"' : ''}
    ${sort ? "order by id desc" : ""}
    limit ${(page - 1) * pagesize}, ${pagesize}`
}

exports.idSelect = (field, table, id) =>
  `select HEX(id) id, ${field} from ${table} where id = uuid_to_bin('${id}')`

exports.insert = (table, field, data) =>
  `Insert into ${table} (id${field}) values(replace(uuid_to_bin(uuid(), true), _utf8'-', _utf8'')${data})`

exports.idUpdate = (table, data, id) =>
  `update ${table} set ${data} where id = uuid_to_bin('${id}')`

exports.phoneUpdatePwd = (table, password, phone) =>
  `update ${table} set password = '${password}' where phone = ${phone}`

exports.delete = (table, id) =>
  `delete from ${table} where id = uuid_to_bin('${id}')`

exports.selectTransition = (data, f) => {
  let str = ""
  for (const i in data) {
    if (i === 'state' && data[i] < 0) continue
    if (i === "id" && f) str += " and " + i + "=uuid_to_bin('" + data[i] + "')"
    else if (data[i] === 0 || data[i] && i !== "id") {
      if (i.includes("_id")) str += " and " + i + "=uuid_to_bin('" + data[i] + "')"
      else if (typeof data[i] == "string") str += " and " + i + "='" + data[i] + "'"
      else str += " and " + i + "=" + data[i]
    }
  }
  return str
}

exports.updateTransition = (data) => {
  let str = ""
  for (const i in data) {
    if (i.includes("_id")) str += i + "=uuid_to_bin('" + data[i] + "'),"
    else if (data[i] === 0 || i === 'r_number' || i === 'rtime' || data[i] && i !== "id") {
      if (typeof data[i] == "string") str += i + "='" + data[i] + "',"
      else str += i + "=" + data[i] + ","
      if (i === 'state' && (data[i] === 3 || data[i] === 4)) str += 'atime = CURRENT_TIMESTAMP,'
      else if (i === 'state' && (data[i] === 5 || data[i] === 2)) str += 'rtime = CURRENT_TIMESTAMP,'
      else if (i === 'state' && (data[i] === 7 || data[i] === 8)) str += 'etime = CURRENT_TIMESTAMP,'
    }
  }
  return str.slice(0, str.length - 1)
}

exports.insertTransition = (data) => {
  let str = ""
  for (const i in data) {
    if (i === 'id') continue
    str += ", "
    if (i.includes("_id")) str += "uuid_to_bin('" + data[i] + "')"
    else if (data[i] === 0 || data[i]) {
      if (typeof data[i] == "string") str += "'" + data[i] + "'"
      else str += data[i]
    } else str += '""'
  }
  return str
}

exports.insertStr = (data) => {
  let str = ""
  for (const i in data) if (i !== 'id') {
    str += ", " + i
  }
  return str
}
